package com.beone.admin.helper;

import java.io.FileInputStream;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFDataFormat;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.BeanUtils;

import com.alibaba.fastjson.JSON;
import com.base.common.util.DateUtil;
import com.beone.admin.entity.ImportDetail;
import com.beone.admin.entity.TaskTemplate;
import com.beone.admin.entity.TaskTemplateDetail;
import com.beone.admin.utils.ExportExcelUtils;

/**
 * <p>
 * 排班模板辅助
 * </p>
 *
 * @author Pagenny
 * @date 2019-06-01 14:33
 **/
public class TaskTemplateHelper {

    private static Pattern DATE_PATTERN = Pattern.compile("^\\d{4}-\\d{1,2}-\\d{1,2}");

    private static DataFormatter formatter = new DataFormatter();

    private static final String DATE_FORMAT = "yyyy-MM-dd";

    /**
     * 获取文档
     * 
     * @param taskTemplate
     * @param detailList
     * @param rowDataList 待填充数据
     * @return
     */
    public static XSSFWorkbook getWb(TaskTemplate taskTemplate, List<TaskTemplateDetail> detailList, List<ImportDetail> rowDataList) {
        // 列数。+1列时间栏
        int cols = detailList.size() + 1;
        // 属性行是否需要两行（父子结构）
        boolean fieldsNeedTwoRows = getFieldsNeedTwoRows(detailList);
        // 下一行
        int nextRow = 0;

        List<TaskTemplateDetail> fieldList = new ArrayList<>(cols);
        // 将日期列放在第一列
        fieldList.add(getDateField());
        fieldList.addAll(detailList);

        XSSFWorkbook wb = new XSSFWorkbook();
        String sheetName = "Sheet1";
        XSSFSheet sheet = wb.createSheet(sheetName);

        setConfigRow(sheet.createRow(nextRow++), taskTemplate, detailList);

        // 设置第一列日期栏的宽度
        sheet.setColumnWidth(0, 4000);

        XSSFCellStyle titleStyle = ExportExcelUtils.getTitleStyle(wb);
        XSSFCellStyle dataStyle = ExportExcelUtils.getDataStyle(wb);

        sheet.addMergedRegion(new CellRangeAddress(nextRow, nextRow, 0, cols - 1));
        // 标题行
        Row titleRow = sheet.createRow(nextRow++);
        Cell titleCell = titleRow.createCell(0);
        String title = taskTemplate.getDeptName() + taskTemplate.getTitle();
        titleCell.setCellValue(title);
        titleCell.setCellStyle(titleStyle);

        if (fieldsNeedTwoRows) {
            // 合并单元格
            for (int i = 0; i < cols; i++) {
                if (!hasParent(fieldList.get(i))) {
                    // 纵向合并
                    sheet.addMergedRegion(new CellRangeAddress(nextRow, nextRow + 1, i, i));
                } else {
                    int endIndex = getEndIndexSameParent(fieldList, i);
                    if (i != endIndex) {
                        // 横向合并
                        sheet.addMergedRegion(new CellRangeAddress(nextRow, nextRow, i, endIndex));
                        i = endIndex;
                    }
                }
            }

            // 填充字段
            Row rowParent = sheet.createRow(nextRow++);
            Row rowChild = sheet.createRow(nextRow++);
            for (int i = 0; i < cols; i++) {
                if (hasParent(fieldList.get(i))) {
                    Cell cell = rowParent.createCell(i);
                    cell.setCellStyle(dataStyle);
                    cell.setCellValue(fieldList.get(i).getParentName());
                    Cell childCell = rowChild.createCell(i);
                    childCell.setCellStyle(dataStyle);
                    childCell.setCellValue(fieldList.get(i).getFieldName());
                } else {
                    Cell childCell = rowChild.createCell(i);
                    childCell.setCellStyle(dataStyle);
                    Cell cell = rowParent.createCell(i);
                    cell.setCellStyle(dataStyle);
                    cell.setCellValue(fieldList.get(i).getFieldName());
                }
            }
        } else {
            Row fieldRow = sheet.createRow(nextRow++);
            for (int i = 0; i < cols; i++) {
                Cell cell = fieldRow.createCell(i);
                cell.setCellStyle(dataStyle);
                cell.setCellValue(fieldList.get(i).getFieldName());
            }
        }

        // 设置日期为文本格式
        XSSFDataFormat format = wb.createDataFormat();
        dataStyle.setDataFormat(format.getFormat("@"));
        // 在第一列填充日期
        List<String> dateList =
            DateUtil.getDateStrBetween(taskTemplate.getBeginDate(), taskTemplate.getEndDate(), DATE_FORMAT);
        if (CollectionUtils.isEmpty(rowDataList)) {
            // 没有数据则只设置日期
            for (int i = 0; i < dateList.size(); i++) {
                Row dataRow = sheet.createRow(nextRow++);
                Cell cell = dataRow.createCell(0);
                cell.setCellStyle(dataStyle);
                cell.setCellValue(dateList.get(i));
                cell.setCellType(CellType.STRING);
            }
        } else {
            Map<String, List<ImportDetail>> rowDataMap = getMapGroupByDate(rowDataList);
            List<Integer> fieldIdList = fieldList.stream().map(TaskTemplateDetail::getId).collect(Collectors.toList());
            // 有数据则填充
            for (int i = 0; i < dateList.size(); i++) {
                Row dataRow = sheet.createRow(nextRow++);
                Cell cell = dataRow.createCell(0);
                cell.setCellStyle(dataStyle);
                cell.setCellValue(dateList.get(i));
                cell.setCellType(CellType.STRING);

                List<ImportDetail> thisDataList = rowDataMap.get(dateList.get(i));
                if (thisDataList != null) {
                    // 填充数据（跳过日期栏）
                    for (int j = 1; j < cols; j++) {
                        Integer fieldId = fieldIdList.get(j);
                        Cell dataCell = dataRow.createCell(j);
                        dataCell.setCellStyle(dataStyle);
                        dataCell.setCellValue(getDataValue(thisDataList, fieldId));
                    }
                }
            }
        }
        return wb;
    }

    /**
     * 解析 excel，暂定格式：
     * <ol>
     * <li>第一行为系统配置，第一个单元格为TemplateConfig的json字符串，后面的单元格为当前列的模板明细id</li>
     * <li>第一列为日期栏</li>
     * <li>每行数据行的第一个单元格都应该为日期</li>
     * </ol>
     */
    public static ExcelImport readExcel(InputStream inputStream) throws Exception {
        try {
            Workbook workbook = WorkbookFactory.create(inputStream);
            Sheet sheet = workbook.getSheetAt(0);

            Row configRow = sheet.getRow(0);
            TemplateConfig config = new TemplateConfig();
            // 模板明细id数组，第一项为空（因为第一列为配置json串）
            Integer[] templateDetailIdArray = new Integer[configRow.getLastCellNum()];
            List<ImportDetail> detailList = new ArrayList<>();
            buildConfig(configRow, config, templateDetailIdArray);

            SimpleDateFormat sdf = new SimpleDateFormat(DATE_FORMAT);
            for (int i = 1; i <= sheet.getLastRowNum(); i++) {
                Row row = sheet.getRow(i);
                if (!DATE_PATTERN.matcher(formatter.formatCellValue(row.getCell(0))).matches()) {
                    continue;
                }
                Date thisDate = sdf.parse(formatter.formatCellValue(row.getCell(0)));

                for (int j = 1; j < row.getLastCellNum(); j++) {
                    Cell cell = row.getCell(j);
                    ImportDetail detail = new ImportDetail();
                    detail.setTemplateDetailId(templateDetailIdArray[j]);
                    detail.setDeptId(config.getDeptId());
                    detail.setDetailDate(thisDate);
                    detail.setDetailValue(formatter.formatCellValue(cell));
                    detailList.add(detail);
                }
            }
            return new ExcelImport(config, detailList);
        } finally {
            if (inputStream != null) {
                inputStream.close();
            }
        }
    }

    public static void main(String[] args) throws Exception {
        InputStream inputStream = new FileInputStream("E:\\我的文档\\Downloads\\导入模板.xlsx");
        readExcel(inputStream);
    }

    /** +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ */

    /**
     * 组装日期属性到表头
     * 
     * @return
     */
    private static TaskTemplateDetail getDateField() {
        TaskTemplateDetail detail = new TaskTemplateDetail();
        detail.setFieldName("排版日期");
        detail.setChildTag(0);
        return detail;
    }

    /**
     * 设置模板配置参数，并隐藏该行 <br/>
     * 第一个存放模板全局配置参数并转为json，之后的每一行存模板明细id
     * 
     * @param configRow
     * @param taskTemplate
     * @param detailList
     */
    private static void setConfigRow(Row configRow, TaskTemplate taskTemplate, List<TaskTemplateDetail> detailList) {
        int nextCell = 0;
        Cell configCell = configRow.createCell(nextCell++);
        TemplateConfig config = new TemplateConfig();
        config.setDeptId(taskTemplate.getDeptId());
        config.setTemplateId(taskTemplate.getId());
        configCell.setCellValue(JSON.toJSONString(config));

        for (TaskTemplateDetail detail : detailList) {
            Cell cell = configRow.createCell(nextCell++);
            cell.setCellValue(detail.getId());
        }

        // 设置为隐藏
        configRow.setZeroHeight(true);
    }

    /**
     * 从开始下标起，获取同一父级的最后下标
     * 
     * @param detailList
     * @param startIndex
     * @return
     */
    private static int getEndIndexSameParent(List<TaskTemplateDetail> detailList, int startIndex) {
        int endIndex = startIndex;
        String startParentName = detailList.get(startIndex).getParentName();
        for (int i = startIndex + 1; i < detailList.size(); i++) {
            if (!StringUtils.equals(startParentName, detailList.get(i).getParentName())) {
                return endIndex;
            }
            endIndex++;
        }
        return endIndex;
    }

    /**
     * 字段区域是否需要分两行显示
     * 
     * @param detailList
     * @return
     */
    private static boolean getFieldsNeedTwoRows(List<TaskTemplateDetail> detailList) {
        for (int i = 0; i < detailList.size(); i++) {
            if (hasParent(detailList.get(i))) {
                return true;
            }
        }
        return false;
    }

    /**
     * 是否有父级
     * 
     * @param detail
     * @return
     */
    private static boolean hasParent(TaskTemplateDetail detail) {
        return detail.getChildTag() == 1;
    }

    /**
     * 获取每天的数据的Map对象。key为日期，value为当天的所有明细数据
     * @param rowDataList
     * @return
     */
    private static Map<String, List<ImportDetail>> getMapGroupByDate(List<ImportDetail> rowDataList) {
        Map<String, List<ImportDetail>> map = new HashMap<>(16);
        for (ImportDetail importDetail : rowDataList) {
            String date = DateUtil.date2String(importDetail.getDetailDate(), DATE_FORMAT);
            if (map.containsKey(date)) {
                map.get(date).add(importDetail);
            } else {
                List<ImportDetail> list = new ArrayList();
                list.add(importDetail);
                map.put(date, list);
            }
        }
        return map;
    }

    /**
     * 根据列属性id获取值
     * @param dataList
     * @param fieldId
     * @return
     */
    private static String getDataValue(List<ImportDetail> dataList, Integer fieldId) {
        for (int i = 0; i < dataList.size(); i++) {
            ImportDetail importDetail =  dataList.get(i);
            if (fieldId.equals(importDetail.getTemplateDetailId())) {
                return importDetail.getDetailValue();
            }
        }
        return "";
    }

    /**
     * 组装配置信息
     * 
     * @param configRow
     *            配置行
     * @param config
     *            模板配置对象
     * @param fieldIds
     *            模板明细id数组
     */
    private static void buildConfig(Row configRow, TemplateConfig config, Integer[] fieldIds) {
        try {
            TemplateConfig oldConfig =
                JSON.parseObject(formatter.formatCellValue(configRow.getCell(0)), TemplateConfig.class);
            BeanUtils.copyProperties(oldConfig, config);
            for (int i = 1; i < configRow.getLastCellNum(); i++) {
                fieldIds[i] = Integer.parseInt(formatter.formatCellValue(configRow.getCell(i)));
            }
        } catch (Exception e) {
            System.err.println("解析excel文件，获取配置项失败");
            throw new RuntimeException("解析excel文件失败，请检查模板文件是否正确");
        }
    }
}
